package com.eportal.struts.action;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.Socket;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.eportal.ORM.BillBody;
import com.eportal.ORM.Message;
import com.eportal.ORM.MpayOrg;
import com.eportal.ORM.UserView;
import com.eportal.ORM.ViewWtPepPayinfo;
import com.eportal.ORM.WtBillInfo;
import com.eportal.ORM.WtBillInfoId;
import com.eportal.ORM.WtPepJnls;
import com.eportal.ORM.WtPepPayinfo;
import com.eportal.core.BaseAction;
import com.eportal.core.Bill;
import com.eportal.core.Billinfo;
import com.eportal.core.Page;
import com.eportal.core.Util;
import com.eportal.service.AdminService;

public class BillAction extends BaseAction {
	AdminService service;
	AdminService serviceOracle;
	private Page page;
	private String start = "0";
	private String limit = "30";
	private boolean success;
	private String message;
	private String startDate;
	private String endDate;

	// 账单发送的属性
	private List typelist;
	private String billType;
	private String termid;
	private String phoneNo;
	private String content;
	private String billAmt;
	private String billstate;

	public String getBillstate() {
		return billstate;
	}

	public void setBillstate(String billstate) {
		this.billstate = billstate;
	}

	public List getTypelist() {
		return typelist;
	}

	public void setTypelist(List typelist) {
		this.typelist = typelist;
	}

	public String getBillType() {
		return billType;
	}

	public void setBillType(String billType) {
		this.billType = billType;
	}

	public String getTermid() {
		return termid;
	}

	public void setTermid(String termid) {
		this.termid = termid;
	}

	public String getPhoneNo() {
		return phoneNo;
	}

	public void setPhoneNo(String phoneNo) {
		this.phoneNo = phoneNo;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public String getBillAmt() {
		return billAmt;
	}

	public void setBillAmt(String billAmt) {
		this.billAmt = billAmt;
	}

	public AdminService getService() {
		return service;
	}

	public void setService(AdminService service) {
		this.service = service;
	}

	public AdminService getServiceOracle() {
		return serviceOracle;
	}

	public void setServiceOracle(AdminService serviceOracle) {
		this.serviceOracle = serviceOracle;
	}

	public Page getPage() {
		return page;
	}

	public void setPage(Page page) {
		this.page = page;
	}

	public String getStart() {
		return start;
	}

	public void setStart(String start) {
		this.start = start;
	}

	public String getLimit() {
		return limit;
	}

	public void setLimit(String limit) {
		this.limit = limit;
	}

	public boolean isSuccess() {
		return success;
	}

	public void setSuccess(boolean success) {
		this.success = success;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}
	
	public void excelToBill(){
		LOG.info("导出账单数据...");
		UserView m = (UserView) getRequest().getSession().getAttribute(
		"mpay_user");
		String sql = "";
		if (m.getIsAll() == 1) {
			sql += " where '1'='1' ";
		} else {
			sql += " where trim(BILL_ORGCODE) = '"+m.getShopId()+"' or BILL_CALLNO = '"+m.getMpayTermid()+"')";
		}
		startDate = startDate == null ? "" : startDate.trim();
		endDate = endDate == null ? "" : endDate.trim();
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and BILL_RCVDATE between '" + startDate + "' and  '" + endDate
			+ "' ";
		}
		termid = termid == null ? "" : termid.trim();
		if (!termid.equals("")) {
			sql += " and BILL_CALLNO like '%" + termid + "%'";
		}
		billType = billType == null ? "" : billType.trim();
		if (!billType.equals("")) {
			sql += " and BILL_STAN like '" + billType + "%'";
		}
		billstate = billstate == null ? "" : billstate.trim();
		if (!billstate.equals("")) {
			sql += " and BILL_PAYCODE = '" + billstate + "'";
		}
		sql += " order by BILL_SENDDT desc ";
		System.out.println(sql);
		List tmp = serviceOracle.query("from WtBillInfo" + sql);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("账单号");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("账单内容");
		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("金额");
		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("网点电话");
		cell = row.createCell((short) 4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("状态");
		cell = row.createCell((short) 5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("支付时间");
		cell = row.createCell((short) 6);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("支付卡号");
		cell = row.createCell((short) 7);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("支付流水号");
		cell = row.createCell((short) 8);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("支付检索参考号");
		Iterator itr = tmp.iterator();
		WtBillInfo w;
		short rowNum = 1;
		while (itr.hasNext()) {
			row = sheet.createRow(rowNum);
			w = (WtBillInfo) itr.next();
			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getId().getBillRcvdate()+w.getId().getBillStan());
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillHintmsg());
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(Util.FormatSum(w.getBillTxamt()));
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillCallno());
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillPaycode().equals("FF")?"未支付":"已支付");
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			String value = w.getBillPaydate();
			if(value!=null)
			value = value.substring(0,2)+'-'+value.substring(2,4)+' '+value.substring(4,6)+':'+value.substring(6,8)+':'+value.substring(8,10);
			cell.setCellValue(w.getBillPaydate()==null?"-":value);
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillCardno()==null?"-": Util.toInCard(w.getBillCardno().trim()));
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillPestan()==null?"-":w.getBillPestan());
			cell = row.createCell((short) 8);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(w.getBillPaystan()==null?"-":w.getBillPaystan());
			rowNum++;
		}
		try {
			OutputStream out = this.getResponse().getOutputStream();
			this.getResponse().setHeader("Content-disposition",
					"attachment; filename=bill_data.xls");
			this.getResponse().setContentType(
					"application/msexcel;charset=UTF-8");
			wb.write(out);
			out.flush();
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String billList() {
		LOG.info("读取账单数据...");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		String sql = "";
		if (m.getIsAll() == 1) {
			sql += " where '1'='1' ";
		} else {
			sql += " where trim(BILL_ORGCODE) = '"+m.getShopId()+"' or BILL_CALLNO = '"+m.getMpayTermid()+"')";
		}
		startDate = startDate == null ? "" : startDate.trim();
		endDate = endDate == null ? "" : endDate.trim();
		if (!startDate.equals("") && !endDate.equals("")) {
			startDate = startDate.substring(0,startDate.indexOf("T"));
			endDate = endDate.substring(0,endDate.indexOf("T"));
			startDate = startDate.replace("-", "");
			endDate = endDate.replace("-", "");
			sql += " and BILL_RCVDATE between '" + startDate.substring(2) + "' and  '" + endDate.substring(2)
					+ "' ";
		}
		termid = termid == null ? "" : termid.trim();
		if (!termid.equals("")) {
			sql += " and BILL_CALLNO like '%" + termid + "%'";
		}
		billType = billType == null ? "" : billType.trim();
		if (!billType.equals("")) {
			sql += " and BILL_STAN like '" + billType + "%'";
		}
		billstate = billstate == null ? "" : billstate.trim();
		if (!billstate.equals("")) {
			sql += " and BILL_PAYCODE = '" + billstate + "'";
		}
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		// System.out.println(sql);
		sql += " order by BILL_SENDDT desc ";
		System.out.println(sql);
		List userList = serviceOracle.query("from WtBillInfo" + sql,
				start, l);
		List tmp = new ArrayList();
		for(int i = 0;i<userList.size();i++){
			WtBillInfo w = (WtBillInfo)userList.get(i);
			Billinfo b = new Billinfo();
			b.setBillAddress(w.getBillAddress());
			b.setBillCallno(w.getBillCallno());
			b.setBillCardno(w.getBillCardno()==null?"":Util.toInCard(w.getBillCardno().trim()));
			b.setBillCreatetime(w.getBillCreatetime());
			b.setBillExpdt(w.getBillExpdt());
			b.setBillHintlen(w.getBillHintlen());
			b.setBillHintmsg(w.getBillHintmsg());
			b.setBillIncdno(w.getBillIncdno());
			b.setBillMercode(w.getBillMercode());
			b.setBillMobile(w.getBillMobile());
			b.setBillOpflag(w.getBillOpflag());
			b.setBillOrgcode(w.getId().getBillOrgcode());
			b.setBillPaycode(w.getBillPaycode());
			b.setBillPaydate(w.getBillPaydate());
			b.setBillPayqs(w.getBillPayqs());
			b.setBillPaystan(w.getBillPaystan());
			b.setBillPayterm(w.getBillPayterm());
			b.setBillPestan(w.getBillPestan());
			b.setBillRcvdate(w.getId().getBillRcvdate());
			b.setBillRcvtime(w.getBillRcvtime());
			b.setBillSenddt(w.getBillSenddt());
			b.setBillSignlen(w.getBillSignlen());
			b.setBillSignmsg(w.getBillSignmsg());
			b.setBillSndnum(w.getBillSndnum());
			b.setBillStan(w.getId().getBillRcvdate()+w.getId().getBillStan());
			b.setBillStatus(w.getBillStatus());
			b.setBillTermcode(w.getBillTermcode());
			b.setBillTxamt(w.getBillTxamt());
			b.setBillType(w.getBillType());
			tmp.add(b);
		}
		page = new Page();
		page.setRoot(tmp);
		page.setTotalProperty(serviceOracle
				.countQuery("select count(*) from WtBillInfo" + sql));
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		return SUCCESS;
	}

	public String termList() {
		LOG.info("读取网点信息_" + termid);
		typelist = new ArrayList();
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		if (termid != null && !termid.equals("")) {
			termid = termid.trim();
			if (m.getIsAll() == 0)
				typelist = this.service
						.query(" from MpayOrg where termid like '%" + termid
								+ "%' and shop_id = '" + m.getShopId() + "'");
			else
				typelist = this.service
						.query(" from MpayOrg where termid like '%" + termid
								+ "%'");
		}
		return SUCCESS;
	}

	public String typelist() {
		typelist = new ArrayList();
		typelist = this.serviceOracle
				.query("from WtBillType where bill_flag = '0' ");
		return SUCCESS;
	}

	public String sendbill() {
		LOG.info("发送账单...");
		Bill bill = new Bill();
		UserView m = (UserView) getRequest().getSession().getAttribute(
		"mpay_user");
		bill.setBill_org(m.getShopId());
		bill.setBill_send_date(Util.getCurrentDate("yyMMdd"));
		 Connection c = serviceOracle.getConnection();
		    int p = 0;
		    try {
				PreparedStatement ps = c.prepareStatement("select nvl(substr(bill_stan,3,length(bill_stan)),0) from wt_bill_info where bill_stan like '"+billType.substring(0,2)+"%' order by bill_rcvdate desc ,substr(bill_stan,3,length(bill_stan)) desc");
				ResultSet rset = ps.executeQuery();
				rset.next();
				p= rset.getInt(1);
				p=p+1;
				if(p>9999)
					p=0;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		bill.setBill_stan( billType.substring(0,2)+Util.addZero(p+"", 4));
		bill.setBill_send_time(Util.getCurrentDate("HHmmss"));
		bill.setBill_type("2001");
		bill.setBill_termid(termid);
		bill.setBill_phone(phoneNo);
		bill.setBill_timeout(Util.getNextDateTime(7, "yyyyMMddHHmmss"));
		bill.setBill_tercode("48000088");
		bill.setBill_mercode("860010038980010");
		bill.setBill_incdno("6228333344445555");
		bill.setBill_amt(Util.addZero(Util.moveRightTwo(billAmt), 12));
		bill.setBill_msg(content);
		bill.setBill_head("2");
		bill.setBill_send_type("01");
		try {
			bill.setBill_msg_len(content.getBytes("GBK").length+"");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		bill.setBill_sign_len("000");
		try {
			bill.setBill_length(bill.billBody().getBytes("GBK").length+"");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
//			System.out.println(bill.toString());
			Util.sendBillToServer(bill);
			success = true;
			message = "发送成功!请在账单信息中查看结果";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = true;
			message = "发送失败!";
		}finally{
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return SUCCESS;
	}
}